CREATE PROCEDURE sp_GetForumPosts @UserID          int
                            ,@Year            int
                            ,@Month           int
                            ,@Day             int
                            ,@StartIndex      int
                            ,@NumberOfRecords int
                            ,@Type            varchar(10)
                            ,@Status          varchar (10)
                            ,@CategoryID      int
                            ,@PartnerID         int
                            ,@OrderBy         varchar(20)
                            ,@TotalRecords    int output
AS
   DECLARE @SelectSQL  VARCHAR(4000)
          ,@WHERE      INT 

   SELECT @WHERE = 0
   SELECT @SelectSQL = 'SELECT PostID
                        ,CategoryID
                         ,ParentPostID
                         ,Subject
                         ,Body
                         ,CreateDate
                         ,Status
                         ,Summary
                         ,Type
                         ,UserID
                         ,NumberOfVisits
                          ,PartnerID
                         ,OriginAuthor
                         ,URL
                         ,OriginalDate
                         ,LastModifiedBy
                         ,LastModifiedDateTime  FROM ForumPosts '

   IF ISNULL(@UserID, 0) > 0
   BEGIN
      SELECT @SelectSQL = @SelectSQL + ' WHERE UserId = ' + CAST(@UserID AS VARCHAR(20))
      SET @WHERE = 1
   END

   IF ISNULL(@Year, 0) > 0
      IF @WHERE = 0
      BEGIN
         SELECT @SelectSQL = @SelectSQL + ' WHERE YEAR(CreateDate) = ' + CAST(@Year AS VARCHAR(20))
         SET @WHERE = 1
      END
      ELSE
      BEGIN
         SELECT @SelectSQL = @SelectSQL + ' AND YEAR(CreateDate) = ' + CAST(@Year AS VARCHAR(20))
      END

   IF ISNULL(@Month, 0) > 0
      IF @WHERE = 0
      BEGIN
         SELECT @SelectSQL = @SelectSQL + ' WHERE MONTH(CreateDate) = ' + CAST(@Month AS VARCHAR(20))
         SET @WHERE = 1
      END
      ELSE
      BEGIN
         SELECT @SelectSQL = @SelectSQL + ' AND MONTH(CreateDate) = ' + CAST(@Month AS VARCHAR(20))
      END

   IF ISNULL(@Day, 0) > 0
      IF @WHERE = 0
      BEGIN
         SELECT @SelectSQL = @SelectSQL + ' WHERE DAY(CreateDate) = ' + CAST(@Day AS VARCHAR(20))
         SET @WHERE = 1
      END
      ELSE
      BEGIN
         SELECT @SelectSQL = @SelectSQL + ' AND DAY(CreateDate) = ' + CAST(@Day AS VARCHAR(20))
      END

   IF ISNULL(@CategoryId, 0) > 0
      IF @WHERE = 0
      BEGIN
         SELECT @SelectSQL = @SelectSQL + ' WHERE CategoryId = ' + CAST(@CategoryId AS VARCHAR(20))
         SET @WHERE = 1
      END
      ELSE
      BEGIN
         SELECT @SelectSQL = @SelectSQL + ' AND CategoryId = ' + CAST(@CategoryId AS VARCHAR(20))
      END

  IF ISNULL(@PartnerId, 0) > 0
      IF @WHERE = 0
      BEGIN
         SELECT @SelectSQL = @SelectSQL + ' WHERE PartnerId = ' + CAST(@PartnerId AS VARCHAR(20))
         SET @WHERE = 1
      END
      ELSE
      BEGIN
         SELECT @SelectSQL = @SelectSQL + ' AND PartnerId = ' + CAST(@PartnerId AS VARCHAR(20))
      END

   
   IF LEN(ISNULL(@Status, '')) > 0
      IF @WHERE = 0
      BEGIN
         SELECT @SelectSQL = @SelectSQL + ' WHERE Status = ''' + @Status + ''''
         SET @WHERE = 1
   END
   ELSE
      SELECT @SelectSQL = @SelectSQL + ' AND Status = ''' + @Status +  ''''

   IF LEN(ISNULL(@Type, '')) > 0
      IF @WHERE = 0
      BEGIN
         SELECT @SelectSQL = @SelectSQL + ' WHERE Type = ''' + @Type + '''' 
         SET @WHERE = 1
      END
      ELSE
         SELECT @SelectSQL = @SelectSQL + ' AND Type = ''' + @Type + ''''

   SELECT @SelectSQL = @SelectSQL + ' AND (parentpostid is null OR parentpostid =0)  AND postid  not in (select postid from article) Order By ' + @OrderBy


   /* This is bad that we have to list all the column definition here */
   /* I tried to use 'select into #tempposts from forumposts' and    */
   /* 'alter table #tempposts alter column postid int (4)' but didn't  */
   /* work.                                                                                    */
   /* The main idea is to add a new identity column rownum. That*/
   /* is all because T-SQL does not provide rownum column        */

   create table #tempposts(rownum int identity(1,1)
                          ,PostID  int
                          ,CategoryID int
                          ,ParentPostID int
                          ,Subject nvarchar(400)
                          ,Body ntext
                          ,CreateDate datetime
                          ,Status varchar(10)
                          ,Summary nvarchar(2000)
                          ,Type varchar(50)
                          ,UserID int
                          ,NumberOfVisits int
                          ,PartnerID int
                          ,OriginAuthor nvarchar(400)
                          ,URL varchar(1000)
                          ,OriginalDate datetime
                          ,LastModifiedBy int
                          ,LastModifiedDateTime datetime
                          )

   insert into #tempposts(PostID
                        ,CategoryID
                         ,ParentPostID
                         ,Subject
                         ,Body
                         ,CreateDate
                         ,Status
                         ,Summary
                         ,Type
                         ,UserID
                         ,NumberOfVisits
                          ,PartnerID
                         ,OriginAuthor
                         ,URL
                         ,OriginalDate
                         ,LastModifiedBy
                         ,LastModifiedDateTime)
                     EXEC(@SelectSQL)

   select @TotalRecords = count(*) from #tempposts

   if (@StartIndex = 0 or @NumberOfRecords = 0)
      select *,(select count(postid)  from forumposts where parentpostid = tp.postid) as NumberReplies
      from   #tempposts tp
      left join ForumCategories
          on tp.CategoryID = ForumCategories.CategoryID
      left join Users
          on tp.UserID = Users.UserID
      left join Partner
         on tp.PartnerID = Partner.PartnerID
   else
      select  *,(select count(postid)  from forumposts where parentpostid = tp.postid) as NumberReplies
      from   #tempposts tp
      left join ForumCategories
          on tp.CategoryID = ForumCategories.CategoryID
      left join Users
          on tp.UserID = Users.UserID
      left join Partner
         on tp.PartnerID = Partner.PartnerID
      where  rownum >= @StartIndex
      And    rownum <  @StartIndex + @NumberOfRecords

   Drop table #tempposts
GO
